package com.ptadmin.report.executor.processor;

import java.io.File;
import java.util.Iterator;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import com.google.gson.JsonParser;
import com.ptadmin.report.postgresql.PostgreSqlHelper;

public class BrowseTreeReportPGProcessor2 {
	SAXReader reader = new SAXReader();
	JsonParser parser = new JsonParser();
	private String tableName = "\"mwsBrowseTreeNode\"";
	PostgreSqlHelper sqlHelper = new PostgreSqlHelper();

	public void process(ProcessReportsParameter param) {
		try {
			System.out.println(String.format("deleting from table: " + tableName));
			String deleteSql = "DELETE FROM " + tableName;
			sqlHelper.executeSqlString(deleteSql);
			System.out.println("deleted");
			
			String reportFilePath = param.getReportFilePath();
			
			Document document = reader.read(new File(reportFilePath));
			Element rootElement = document.getRootElement();

			Iterator it = rootElement.elementIterator();
			int count = 1;
			String sql_value = "";
			while (it.hasNext()) {
				Element element = (Element) it.next();
				String xml = element.asXML();
				
				String hasChildren = "0";
				Boolean hasChildren_bool = Boolean.parseBoolean(element.elementText("hasChildren").trim());
				if (hasChildren_bool) {
					hasChildren = "1";
				}
				String browseNodeId = element.elementText("browseNodeId");
				String browsePathById = element.elementText("browsePathById");
				String parentBrowseNodeId = "";
				String departmentNodeId = "";
				String categoryNodeId = "";
				
				int len = browsePathById.split(",").length;
				if (len > 1) {
					parentBrowseNodeId = browsePathById.split(",")[len - 1 - 1];
					departmentNodeId = browsePathById.split(",")[0];
					categoryNodeId = browsePathById.split(",")[1];
				}
				
				String browseTreeNodeName = element.elementText("browseNodeName");
				
//				String json = org.json.XML.toJSONObject(xml).toString();

				sql_value += "('" + browsePathById + "', '" + departmentNodeId + "', '" + categoryNodeId + "', '" + browseNodeId + "', '" + parentBrowseNodeId + "', '" + len + "', '" + browseTreeNodeName.replace('\'', '`') + "', '', '" + hasChildren + "'),";
				if (count % 1000 == 0) {
					sql_value = sql_value.substring(0, sql_value.length() - 1) + ";"; 
					String sql = "INSERT INTO " + tableName + " (\"nodeIdPath\",\"departmentId\",\"categoryId\",\"nodeId\",\"parentNodeId\",\"depth\",\"nodeName\",\"nodeNameCN\",\"hasChildren\") " + "VALUES "
							+ sql_value;
					sql_value = "";
					
					sqlHelper.executeSqlString(sql);
					System.out.println(String.format("Opened database successfully, finished %s records", count));
				}

				count++;
			}
			sql_value = sql_value.substring(0, sql_value.length() - 1) + ";";
			String sql = "INSERT INTO " + tableName + " (\"nodeIdPath\",\"departmentId\",\"categoryId\",\"nodeId\",\"parentNodeId\",\"depth\",\"nodeName\",\"nodeNameCN\",\"hasChildren\") " + "VALUES "
					+ sql_value;
			sql_value = "";
			PostgreSqlHelper sqlHelper = new PostgreSqlHelper();
			sqlHelper.executeSqlString(sql);

			System.out.println(String.format("Opened database successfully, finished %s records", count));
			System.out.println("total records: " + count);

		} catch (DocumentException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			System.exit(0);
		}
	}
	
	public static void main(String... args) {
		ProcessReportsParameter param = new ProcessReportsParameter();
		param.setMerchantId("test");
		param.setReportCategory("browseTreeNode");
		param.setReportFilePath("report.xml");
		param.setReportName("browseTreeNode");
		
		BrowseTreeReportPGProcessor2 processor = new BrowseTreeReportPGProcessor2();
		processor.process(param);
	}
}
